package com.pb.infra.param.service;

import com.pb.infra.oth.entity.common.Page;
import com.pb.infra.oth.entity.common.Result;
import com.pb.infra.oth.util.DateTimeUtil;
import com.pb.infra.oth.util.ExcelUtil;
import com.pb.infra.oth.util.JwtTokenConfig;
import com.pb.infra.oth.util.StringUtil;
import com.pb.infra.param.entity.pbbase.*;
import com.pb.infra.param.mapper.ImportConfigMapper;
import com.pb.infra.param.mapper.pbbase.DataImpChekruleMapper;
import com.pb.infra.param.mapper.pbbase.DataImpConfColmapMapper;
import com.pb.infra.param.mapper.pbbase.DataImpConfMapper;
import io.jsonwebtoken.Claims;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 导入配置管理
 * @author liudebao
 * @date 2021/02/22
 */
@Service
public class ImportConfigService {

    /**
     * 导入配置多表DAO映射
     */
    @Resource
    private ImportConfigMapper importConfigMapper;

    /**
     * jwttoken配置类
     */
    @Resource
    private JwtTokenConfig jwtTokenConfig;

    /**
     * 导入配置单表DAO映射
     */
    @Resource
    private DataImpConfMapper dataImpConfMapper;

    /**
     * 导入检查规则表DAO映射
     */
    @Resource
    private DataImpChekruleMapper dataImpChekruleMapper;

    /**
     * 字段映射表DAO映射
     */
    @Resource
    private DataImpConfColmapMapper dataImpConfColmapMapper;

    /**
     * 查询分页数据
     *
     * @param map 查询参数
     * @param page 页码
     * @return
     */
    public Result selectByPage(Map<String, Object> map, Page page, HttpServletRequest request){
        String token = request.getHeader(jwtTokenConfig.getHeader());
        Claims claims = jwtTokenConfig.getClaimByToken(token);
        String userId = (String)claims.get(jwtTokenConfig.getSub());
        Integer pageIndex = Integer.parseInt(map.get("pageIndex").toString());
        Integer pageSize = Integer.parseInt(map.get("pageSize").toString());
        Integer pageEnd = pageSize * pageIndex;
        Integer pageStart = (pageIndex - 1) * pageSize;
        map.put("pageEnd", pageEnd);
        map.put("pageStart", pageStart);
        map.put("tableName", StringUtil.isEmpty(map.get("tableName")) ? null : map.get("tableName").toString().toUpperCase());
        map.put("userId", userId);
        List<Map<String, Object>> list = importConfigMapper.selectByPage(map);
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> map1 = list.get(i);
                String[] strs = map1.get("targetTable").toString().split("\\.");
                map1.put("dbUser", strs[0]);
                map1.put("tableName", strs[1]);
                List<Map<String,Object>> mapList = importConfigMapper.selSourceData(map1);
                list.get(i).put("sourceTable", mapList);
                List<Map<String,Object>> mapList1 = importConfigMapper.selCheckData(map1);
                list.get(i).put("checkTable", mapList1);
            }
        }
        return Result.ok(importConfigMapper.selectByPageCount(map), list);
    }

    /**
     *保存导入配置信息
     * @param paramMap 保存数据
     * @return
     */
    @Transactional
    public Result insert(Map<String, Object> paramMap, HttpServletRequest request){
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String token = request.getHeader(jwtTokenConfig.getHeader());
        Claims claims = jwtTokenConfig.getClaimByToken(token);
        String userId = (String)claims.get(jwtTokenConfig.getSub());
        String cfgId = paramMap.get("cfgId").toString();
        DataImpConf dataImpConf0 = dataImpConfMapper.selectByPrimaryKey(cfgId);
        if (dataImpConf0 != null && !StringUtil.isEmpty(dataImpConf0.getCfgId())) {
            return Result.error("-1", "配置编号已存在");
        } else {
            DataImpConf dataImpConf = new DataImpConf();
            dataImpConf.setCfgId(cfgId);
            dataImpConf.setCfgType(paramMap.get("cfgType").toString());
            dataImpConf.setReplaceMode(paramMap.get("replaceMode").toString());
            if (!StringUtil.isEmpty(paramMap.get("funcDesc"))) {
                dataImpConf.setFuncDesc(paramMap.get("funcDesc").toString());
            }
            dataImpConf.setDataBgnrow(new BigDecimal(paramMap.get("beginRow").toString()));
            dataImpConf.setDataBgncol(new BigDecimal(paramMap.get("beginCol").toString()));
            if (!StringUtil.isEmpty(paramMap.get("dataSource"))) {
                dataImpConf.setDataSource(paramMap.get("dataSource").toString());
            }
            if (!StringUtil.isEmpty(paramMap.get("colDel"))) {
                dataImpConf.setColDel(paramMap.get("colDel").toString());
            }
            if (!StringUtil.isEmpty(paramMap.get("empTyReplace"))) {
                dataImpConf.setEmptyReplace(paramMap.get("empTyReplace").toString());
            }
            if (!StringUtil.isEmpty(paramMap.get("lfReplace"))) {
                dataImpConf.setLfReplace(paramMap.get("lfReplace").toString());
            }
            dataImpConf.setTargetTable(paramMap.get("targetTable").toString());
            dataImpConf.setUpdBy(userId);
            dataImpConf.setUpdDate(format.format(new Date()));
            dataImpConf.setUpdPosition(paramMap.get("positionId").toString());
            dataImpConfMapper.insertSelective(dataImpConf);
            List<Map<String, Object>> table = (List<Map<String, Object>>)paramMap.get("sourceTable");
            if (table != null && table.size() > 0) {
                for (int i = 0; i < table.size(); i++) {
                    Map<String, Object> map1 = table.get(i);
                    DataImpConfColmap dataImpConfColmap = new DataImpConfColmap();
                    dataImpConfColmap.setCfgId(cfgId);
                    //dataImpConfColmap.setDestColLen(map1.get("length").toString());
                    dataImpConfColmap.setDestColNm(map1.get("columnName").toString());
                    dataImpConfColmap.setDestColType(map1.get("type").toString());
                    if (!StringUtil.isEmpty(map1.get("sourceColLen"))) {
                        dataImpConfColmap.setSourceColLen(map1.get("sourceColLen").toString());
                    }
                    dataImpConfColmap.setSourceColNm(map1.get("columnSeq").toString());
                    dataImpConfColmap.setSourceColType(map1.get("sourceType").toString());
                    if (!StringUtil.isEmpty(map1.get("function"))) {
                        dataImpConfColmap.setTransFunc(map1.get("function").toString());
                    }
                    dataImpConfColmap.setUpdDate(format.format(new Date()));
                    dataImpConfColmap.setSeqid(new BigDecimal(i + 1));
                    dataImpConfColmap.setNullFlag(map1.get("nullFlag").toString());
                    dataImpConfColmap.setPkFlag(map1.get("pkFlag").toString());
                    dataImpConfColmapMapper.insertSelective(dataImpConfColmap);
                }
                List<Map<String, Object>> checkTable = (List<Map<String, Object>>)paramMap.get("checkTable");
                if (checkTable != null && checkTable.size() > 0) {
                    for (int j = 0; j < checkTable.size(); j++) {
                        Map<String, Object> map2 = checkTable.get(j);
                        DataImpChekrule dataImpChekrule = new DataImpChekrule();
                        dataImpChekrule.setCfgId(cfgId);
                        dataImpChekrule.setCheckType(map2.get("ruleType").toString());
                        //dataImpChekrule.setEffectFlag();
                        dataImpChekrule.setFormula(map2.get("ruleFormula").toString());
                        dataImpChekrule.setSourceColNm(map2.get("columnSeq").toString());
                        dataImpChekrule.setUpdBy(userId);
                        dataImpChekrule.setUpdDate(format.format(new Date()));
                        dataImpChekrule.setSeqid(new BigDecimal(j + 1));
                        dataImpChekruleMapper.insertSelective(dataImpChekrule);
                    }
                }
            }
        }
        return Result.OK_EMPTY;
    }

    /**
     * 查询数据库用户
     *
     * @param  appMap 应用标识
     * @return
     */
    public Result getBaseUserList(Map<String,Object> appMap){
        List<Map<String,Object>> list =importConfigMapper.getBaseUserList(appMap);
        return Result.ok(list);
    }

    /*public Result getBaseUserData(Map<String,Object> appMap){
        List<Map<String,Object>> list =importConfigMapper.getBaseUserList(appMap);
        return Result.ok(list);
    }*/

    /**
     * 查询源表字段集合
     * @param appMap
     * @return
     */
    public Result getColList(Map<String,Object> appMap){
        List<Map<String,Object>> list =importConfigMapper.getColList(appMap);
        return Result.ok(list);
    }

    /**
     * 查询字段函数
     * @return
     */
    public Result selFunc(){
        List<Map<String,Object>> list =importConfigMapper.selFunc();
        return Result.ok(list);
    }

    /**
     *修改导入配置信息
     * @param paramMap 保存数据
     * @return
     */
    @Transactional
    public Result update(Map<String, Object> paramMap, HttpServletRequest request){
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String token = request.getHeader(jwtTokenConfig.getHeader());
        Claims claims = jwtTokenConfig.getClaimByToken(token);
        String userId = (String)claims.get(jwtTokenConfig.getSub());
        if (!paramMap.get("cfgId").toString().equals(paramMap.get("sourceId").toString())) {
            DataImpConf dataImpConf0 = dataImpConfMapper.selectByPrimaryKey(paramMap.get("cfgId").toString());
            if (dataImpConf0 != null && !StringUtil.isEmpty(dataImpConf0.getCfgId())) {
                return Result.error("-1", "配置编号已存在");
            }
        }
        DataImpConf dataImpConf = new DataImpConf();
        dataImpConf.setCfgId(paramMap.get("cfgId").toString());
        dataImpConf.setCfgType(paramMap.get("cfgType").toString());
        dataImpConf.setReplaceMode(paramMap.get("replaceMode").toString());
        if (!StringUtil.isEmpty(paramMap.get("funcDesc"))) {
            dataImpConf.setFuncDesc(paramMap.get("funcDesc").toString());
        }
        dataImpConf.setDataBgnrow(new BigDecimal(paramMap.get("beginRow").toString()));
        dataImpConf.setDataBgncol(new BigDecimal(paramMap.get("beginCol").toString()));
        if (!StringUtil.isEmpty(paramMap.get("dataSource"))) {
            dataImpConf.setDataSource(paramMap.get("dataSource").toString());
        }
        if (!StringUtil.isEmpty(paramMap.get("colDel"))) {
            dataImpConf.setColDel(paramMap.get("colDel").toString());
        }
        if (!StringUtil.isEmpty(paramMap.get("empTyReplace"))) {
            dataImpConf.setEmptyReplace(paramMap.get("empTyReplace").toString());
        }
        if (!StringUtil.isEmpty(paramMap.get("lfReplace"))) {
            dataImpConf.setLfReplace(paramMap.get("lfReplace").toString());
        }
        dataImpConf.setTargetTable(paramMap.get("targetTable").toString());
        dataImpConf.setUpdBy(userId);
        dataImpConf.setUpdDate(format.format(new Date()));
        dataImpConf.setUpdPosition(paramMap.get("positionId").toString());
        dataImpConfMapper.updateByPrimaryKeySelective(dataImpConf);
        List<Map<String, Object>> table = (List<Map<String, Object>>)paramMap.get("sourceTable");
        if (table != null && table.size() > 0) {
            DataImpConfColmapExample dataImpConfColmapExample = new DataImpConfColmapExample();
            DataImpConfColmapExample.Criteria criteria = dataImpConfColmapExample.createCriteria();
            criteria.andCfgIdEqualTo(paramMap.get("cfgId").toString());
            dataImpConfColmapMapper.deleteByExample(dataImpConfColmapExample);
            for (int i = 0; i < table.size(); i++) {
                Map<String, Object> map1 = table.get(i);
                DataImpConfColmap dataImpConfColmap = new DataImpConfColmap();
                dataImpConfColmap.setCfgId(paramMap.get("cfgId").toString());
                //dataImpConfColmap.setDestColLen(map1.get("length").toString());
                dataImpConfColmap.setDestColNm(map1.get("columnName").toString());
                dataImpConfColmap.setDestColType(map1.get("type").toString());
                if (!StringUtil.isEmpty(map1.get("sourceColLen"))) {
                    dataImpConfColmap.setSourceColLen(map1.get("sourceColLen").toString());
                }
                dataImpConfColmap.setSourceColNm(map1.get("columnSeq").toString());
                dataImpConfColmap.setSourceColType(map1.get("sourceType").toString());
                if (!StringUtil.isEmpty(map1.get("function"))) {
                    dataImpConfColmap.setTransFunc(map1.get("function").toString());
                }
                dataImpConfColmap.setUpdDate(format.format(new Date()));
                dataImpConfColmap.setSeqid(new BigDecimal(i + 1));
                dataImpConfColmap.setNullFlag(map1.get("nullFlag").toString());
                dataImpConfColmap.setPkFlag(map1.get("pkFlag").toString());
                dataImpConfColmapMapper.insertSelective(dataImpConfColmap);
            }
            List<Map<String, Object>> checkTable = (List<Map<String, Object>>)paramMap.get("checkTable");
            DataImpChekruleExample dataImpChekruleExample = new DataImpChekruleExample();
            DataImpChekruleExample.Criteria criteria1 = dataImpChekruleExample.createCriteria();
            criteria1.andCfgIdEqualTo(paramMap.get("cfgId").toString());
            dataImpChekruleMapper.deleteByExample(dataImpChekruleExample);
            if (checkTable != null && checkTable.size() > 0) {
                for (int j = 0; j < checkTable.size(); j++) {
                    Map<String, Object> map2 = checkTable.get(j);
                    DataImpChekrule dataImpChekrule = new DataImpChekrule();
                    dataImpChekrule.setCfgId(paramMap.get("cfgId").toString());
                    dataImpChekrule.setCheckType(map2.get("ruleType").toString());
                    //dataImpChekrule.setEffectFlag();
                    dataImpChekrule.setFormula(map2.get("ruleFormula").toString());
                    dataImpChekrule.setSourceColNm(map2.get("columnSeq").toString());
                    dataImpChekrule.setUpdBy(userId);
                    dataImpChekrule.setUpdDate(format.format(new Date()));
                    dataImpChekrule.setSeqid(new BigDecimal(j + 1));
                    dataImpChekruleMapper.insertSelective(dataImpChekrule);
                }
            }
        }
        return Result.OK_EMPTY;
    }

    /**
     * 删除导入配置信息
     *
     * @param paramMap 请求参数
     */
    @Transactional
    public void delete(List<String> paramMap) {
        for (String cfgId:paramMap) {
            DataImpChekruleExample dataImpChekruleExample = new DataImpChekruleExample();
            DataImpChekruleExample.Criteria criteria1 = dataImpChekruleExample.createCriteria();
            criteria1.andCfgIdEqualTo(cfgId);
            dataImpChekruleMapper.deleteByExample(dataImpChekruleExample);
            DataImpConfColmapExample dataImpConfColmapExample = new DataImpConfColmapExample();
            DataImpConfColmapExample.Criteria criteria = dataImpConfColmapExample.createCriteria();
            criteria.andCfgIdEqualTo(cfgId);
            dataImpConfColmapMapper.deleteByExample(dataImpConfColmapExample);
            dataImpConfMapper.deleteByPrimaryKey(cfgId);
        }
    }

    /**
     * 获取文件递归
     * @param dir
     * @param allFile
     * @return
     */
    public static List<String> getAllFiles(File dir, List<String> allFile)
    {
        File[] files=dir.listFiles();
        for(int i=0; i < files.length; i++) {
            if(files[i].isDirectory()) {
                //这里面用了递归的算法
                getAllFiles(files[i], allFile);
            } else {
                allFile.add(files[i].toString());
            }
        }
        return allFile;
    }

    /**
     * 导入excel文件
     * @param excelFile
     * @param paramMap
     * @return
     */
    public Result importExcel(MultipartFile excelFile, Map<String, Object> paramMap) {
        Map<String,Object> mapList = new HashMap<>();
        InputStream input = null;
        try {
            ZipSecureFile.setMinInflateRatio(-1.0d);
            String cfgId = paramMap.get("cfgId").toString();
            DataImpConf dataImpConf = dataImpConfMapper.selectByPrimaryKey(cfgId);
            if (dataImpConf != null) {
                Integer beginRow = Integer.parseInt(dataImpConf.getDataBgnrow().toPlainString());
                Integer beginCol = Integer.parseInt(dataImpConf.getDataBgncol().toPlainString());
                String mode = dataImpConf.getReplaceMode();
                String targetTab = dataImpConf.getTargetTable();
                DataImpConfColmapExample dataImpConfColmapExample = new DataImpConfColmapExample();
                DataImpConfColmapExample.Criteria criteria = dataImpConfColmapExample.createCriteria();
                criteria.andCfgIdEqualTo(cfgId);
                List<Map<String,Object>> list = importConfigMapper.colImpData(paramMap);
                List<String> colList = new ArrayList<>();
                List<String> pkStr = new ArrayList<>();
                List<Map<String,Object>> nullList = new ArrayList<>();
                StringBuffer name = new StringBuffer();
                List<Map<String,Object>> pkData = new ArrayList<>();
                for (int j = 0; j < list.size(); j++) {
                    Map<String,Object> objectMap = list.get(j);
                    colList.add(objectMap.get("colName").toString());
                    if ("1".equals(objectMap.get("pkFlag").toString())) {
                        pkStr.add(objectMap.get("sourceCol").toString());
                        if ("".equals(name.toString())) {
                            name.append(objectMap.get("colName").toString());
                        } else {
                            name.append("," + objectMap.get("colName").toString());
                        }
                        Map<String,Object> objectMap1 = new HashMap<>();
                        objectMap1.put("colName", objectMap.get("colName").toString());
                        pkData.add(objectMap1);
                    }
                    if ("0".equals(objectMap.get("nullFlag").toString())) {
                        Map<String,Object> objectMap2 = new HashMap<>();
                        objectMap2.put("colName", objectMap.get("colName").toString());
                        objectMap2.put("sourceCol", objectMap.get("sourceCol").toString());
                        nullList.add(objectMap2);
                    }
                }
                if ("WEBXLSX".equals(dataImpConf.getCfgType())) {
                    if (list != null && list.size() > 0) {
                        input = excelFile.getInputStream();
                        String fileName = excelFile.getOriginalFilename();
                        Workbook workbook = null;
                        if (fileName.endsWith("xls")) {
                            workbook = new HSSFWorkbook(input);
                        } else if (fileName.endsWith("xlsx")) {
                            workbook = new XSSFWorkbook(input);
                        }
                        boolean hasErr = false;                                                      //存在错误标志位
                        StringBuilder errInfo = new StringBuilder();
                        Integer seq = importConfigMapper.selMaxSeq(paramMap);
                        Sheet sheet = workbook.getSheetAt(0);
                        int rows = sheet.getPhysicalNumberOfRows();
                        List<String> key=new ArrayList<>();
                        List<Map<String, Object>> arrList = new ArrayList<>();
                        errInfo.append("<br/><br/><br/>*** 开始检查"+ workbook.getSheetName(0) +" ***");
                        for (int i = beginRow - 1; i < rows; i++) {
                            Row row = sheet.getRow(i);
                            if (row != null) {
                                // 校验主键重复
                                boolean rowFlag=true;
                                StringBuffer rowBuffer = new StringBuffer();
                                StringBuffer buffer = new StringBuffer();
                                if (rowBuffer.toString().length()==0){
                                    rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第<b>"+(i+1)+"</b>行数据:");
                                }
                                for (int j = 0; j < pkStr.size(); j++) {
                                    int colSeq = beginCol + Integer.parseInt(pkStr.get(j)) - 2;
                                    String cellValue = ExcelUtil.getStringCellValue(row.getCell(colSeq));
                                    buffer.append(cellValue);
                                    pkData.get(j).put("value", cellValue);
                                }
                                if (key.contains(buffer.toString())){
                                    rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + name.toString() + "字段数据重复");
                                    rowFlag=false;
                                }else {
                                    if ("ADDEND".equals(mode) && pkData.size() > 0) {
                                        String sql = "select * from " + targetTab + " where ";
                                        String str = "";
                                        for (int t = 0; t < pkData.size(); t++) {
                                            if ("".equals(str)) {
                                                str = str + pkData.get(t).get("colName").toString() + "=" + pkData.get(t).get("value").toString();
                                            } else {
                                                str = str + "and" + pkData.get(t).get("colName").toString() + "=" + pkData.get(t).get("value").toString();
                                            }
                                        }
                                        sql = sql + str;
                                        Map<String,Object> map2 = new HashMap<>();
                                        map2.put("sql", sql);
                                        List<Map<String,Object>> list1 = importConfigMapper.selPkData(map2);
                                        if (list1 != null && list1.size() > 0) {
                                            rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + name.toString() + "数据库字段数据重复");
                                            rowFlag=false;
                                        } else {
                                            key.add(buffer.toString());
                                        }
                                    } else {
                                        key.add(buffer.toString());
                                    }
                                }
                                Map<String, Object> mapData = new HashMap<>();
                                for (int j = beginCol - 1; j < seq + (beginCol - 1); j++) {
                                    Cell cell = row.getCell(j);
                                    String val = ExcelUtil.getStringCellValue(cell);
                                    for (int t = 0; t < nullList.size(); t++) {
                                        int m = beginCol + Integer.parseInt(nullList.get(t).get("sourceCol").toString()) - 2;
                                        if (j == m && StringUtil.isEmpty(val)) {
                                            rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + nullList.get(t).get("colName").toString() + ">不能为空");
                                            rowFlag=false;
                                        }
                                    }
                                    if (!StringUtil.isEmpty(val)) {
                                        for (int t = 0; t < list.size(); t++) {
                                            if ("NOSTR".equals(list.get(t).get("sourceColType").toString())) {
                                                if (!StringUtil.isEmpty(list.get(t).get("transFunc"))) {
                                                    val = list.get(t).get("transFunc").toString();
                                                } else {
                                                    val = "";
                                                }
                                            }
                                            int colSeq = Integer.parseInt(list.get(t).get("sourceCol").toString());
                                            if (colSeq + beginCol - 2 == j) {
                                                if (!StringUtil.isEmpty(list.get(t).get("transFunc")) && !"NOSTR".equals(list.get(t).get("sourceColType").toString())) {
                                                    mapData.put(list.get(t).get("colName").toString(), val + "@@_&&" + list.get(t).get("transFunc").toString());
                                                } else {
                                                    mapData.put(list.get(t).get("colName").toString(), val + "@@_&&nofunction");
                                                }
                                                if (!StringUtil.isEmpty(list.get(t).get("checkType"))) {
                                                    String checkType = list.get(t).get("checkType").toString();
                                                    String formula = list.get(t).get("formula").toString();
                                                    if ("VALIN".equals(checkType)) {
                                                        //列表
                                                        String[] strings = formula.split(",");
                                                        boolean flag = false;
                                                        for (int s = 0; s < strings.length; s++) {
                                                            if (strings[s].equals(val)) {
                                                                flag = true;
                                                                break;
                                                            }
                                                        }
                                                        if (!flag) {
                                                            rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值不在[" + formula + "]列表范围内");
                                                            rowFlag=false;
                                                        }
                                                    } else if ("VALGT".equals(checkType)) {
                                                        //大于
                                                        if (Integer.parseInt(val) > Integer.parseInt(formula)) {
                                                        } else {
                                                            rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值不大于" + formula);
                                                            rowFlag=false;
                                                        }
                                                    } else if ("VALLT".equals(checkType)) {
                                                        //小于
                                                        if (Integer.parseInt(val) < Integer.parseInt(formula)) {
                                                        } else {
                                                            rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值不小于" + formula);
                                                            rowFlag=false;
                                                        }
                                                    } else if ("VALGTE".equals(checkType)) {
                                                        //大于等于
                                                        if (Integer.parseInt(val) > Integer.parseInt(formula) || Integer.parseInt(val) == Integer.parseInt(formula)) {
                                                        } else {
                                                            rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值小于" + formula);
                                                            rowFlag=false;
                                                        }
                                                    } else if ("VALLTE".equals(checkType)) {
                                                        //小于等于
                                                        if (Integer.parseInt(val) < Integer.parseInt(formula) || Integer.parseInt(val) == Integer.parseInt(formula)) {
                                                        } else {
                                                            rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值大于" + formula);
                                                            rowFlag=false;
                                                        }
                                                    } else if ("VALREG".equals(checkType)) {
                                                        //正则
                                                        if (!val.matches(formula)) {
                                                            rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">正则公式不匹配");
                                                            rowFlag=false;
                                                        }

                                                    } else if ("FORMULA".equals(checkType)) {
                                                        //公式

                                                    } else if ("VALEQUAL".equals(checkType)) {
                                                        //等于
                                                        if (val != formula) {
                                                            rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值不等于" + formula);
                                                            rowFlag=false;
                                                        }
                                                    }
                                                }


                                            }

                                        }
                                    }
                                }
                                if (rowFlag) {
                                    //将行错误信息记录到全局错误信息中
                                    arrList.add(mapData);
                                }else {
                                    errInfo.append(rowBuffer.toString());

                                }
                            }
                        }
                        if (!hasErr) {
                            String colStr = "";
                            for (int j = 0; j < colList.size(); j++) {
                                if ("".equals(colStr)) {
                                    colStr = colList.get(j);
                                } else {
                                    colStr = colStr + "," + colList.get(j);
                                }
                            }
                            if ("OVERWRITE".equals(mode)) {
                                paramMap.put("targetTab", targetTab);
                                importConfigMapper.delAllData(paramMap);
                            }
                            //String sql0 = "insert into " + targetTab + " (" + colStr + ")" + " values";
                            String sql0 = "insert into " + targetTab + " (" + colStr + ")";
                            String valStr = "";
                            Map<String, Object> map3 = new HashMap<>();
                            if (arrList.size() > 1000) {
                                for (int j = 0; j < arrList.size(); j++) {
                                    Map<String, Object> map0 = arrList.get(j);
                                    //String a = "(";
                                    String a = "select ";
                                    for (int k = 0; k < colList.size(); k++) {
                                        String ssm = map0.get(colList.get(k)).toString();
                                        String[] b = ssm.split("@@_&&");
                                        String insertVal = "";
                                        if ("nofunction".equals(b[1])) {
                                            insertVal = b[0];
                                        } else {
                                            insertVal = b[0];
                                            if (b[1].indexOf("to_date") > -1) {
                                                String[] strings = b[1].split("\\(");
                                                if (strings.length > 1) {
                                                    String[] strings1 = b[1].split("\\(")[1].split("\\)");
                                                    if (strings1.length > 0) {
                                                        String reg = strings1[0];
                                                        insertVal = DateTimeUtil.matchDate(b[0], reg);
                                                    }
                                                }
                                            } else {
                                            }
                                        }
                                    /*if ("(".equals(a)) {
                                        a = a + "'" + insertVal + "'";
                                    } else {
                                        a = a + ",'" + insertVal + "'";
                                    }*/
                                        if ("select ".equals(a)) {
                                            a = a + "'" + insertVal + "'";
                                        } else {
                                            a = a + ",'" + insertVal + "'";
                                        }
                                    }
                                    //a = a + ")";
                                    a = a + " from dual";
                                    if ("".equals(valStr)) {
                                        valStr = valStr + a;
                                    } else {
                                        //valStr = valStr + "," + a;
                                        valStr = valStr + " union all " + a;
                                    }
                                    if ((j != 0 && j % 1000 == 0) || j == arrList.size() - 1) {
                                        String sql = sql0 + valStr;
                                        map3.put("sql", sql);
                                        importConfigMapper.insertData(map3);
                                        valStr = "";
                                    }
                                }
                            } else {
                                for (int j = 0; j < arrList.size(); j++) {
                                    Map<String, Object> map0 = arrList.get(j);
                                    //String a = "(";
                                    String a = "select ";
                                    for (int k = 0; k < colList.size(); k++) {
                                        String ssm = map0.get(colList.get(k)).toString();
                                        String[] b = ssm.split("@@_&&");
                                        String insertVal = "";
                                        if ("nofunction".equals(b[1])) {
                                            insertVal = b[0];
                                        } else {
                                            insertVal = b[0];
                                            if (b[1].indexOf("to_date") > -1) {
                                                String[] strings = b[1].split("\\(");
                                                if (strings.length > 1) {
                                                    String[] strings1 = b[1].split("\\(")[1].split("\\)");
                                                    if (strings1.length > 0) {
                                                        String reg = strings1[0];
                                                        insertVal = DateTimeUtil.matchDate(b[0], reg);
                                                    }
                                                }
                                            } else {
                                            }
                                        }
                                    /*if ("(".equals(a)) {
                                        a = a + "'" + insertVal + "'";
                                    } else {
                                        a = a + ",'" + insertVal + "'";
                                    }*/
                                        if ("select ".equals(a)) {
                                            a = a + "'" + insertVal + "'";
                                        } else {
                                            a = a + ",'" + insertVal + "'";
                                        }
                                    }
                                    //a = a + ")";
                                    a = a + " from dual";
                                    if ("".equals(valStr)) {
                                        valStr = valStr + a;
                                    } else {
                                        //valStr = valStr + "," + a;
                                        valStr = valStr + " union all " + a;
                                    }
                                }
                                //valStr = valStr + ";";
                                String sql = sql0 + valStr;
                                map3.put("sql", sql);
                                importConfigMapper.insertData(map3);
                            }
                        }
                        if (hasErr) {
                            mapList.put("code", -1);
                            mapList.put("msg", errInfo.toString());
                        } else {
                            StringBuilder rightInfo = new StringBuilder();
                            rightInfo.append("<br/>");
                            rightInfo.append("**********   <span style='color:green;font-weight:bold;'>导入结果：成功</span>   **********");
                            rightInfo.append("<br/><br/>"+ sheet.getSheetName() +" 共计导入 <span style='color:blue;font-weight:bold;'>" + arrList.size() + "</span> 条" + workbook.getSheetAt(0).getSheetName() + "数据");
                            mapList.put("code", 0);
                            mapList.put("msg", rightInfo.toString());
                        }
                    }
                } else {
                    mapList.put("code", -1);
                    mapList.put("msg", "导入类型配置错误");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            mapList.put("code", -1);
            mapList.put("msg", e.getMessage());
        } finally {
            if (null != input) {
                try {
                    input.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return Result.ok(mapList);
    }

    /**
     * 表对表导入
     * @param paramMap
     * @return
     */
    public Result importFileData(Map<String, Object> paramMap) {
        Map<String,Object> mapList = new HashMap<>();
        InputStream input = null;
        try {
            ZipSecureFile.setMinInflateRatio(-1.0d);
            String cfgId = paramMap.get("cfgId").toString();
            DataImpConf dataImpConf = dataImpConfMapper.selectByPrimaryKey(cfgId);
            if (dataImpConf != null) {
                Integer beginRow = Integer.parseInt(dataImpConf.getDataBgnrow().toPlainString());
                Integer beginCol = Integer.parseInt(dataImpConf.getDataBgncol().toPlainString());
                String mode = dataImpConf.getReplaceMode();
                String targetTab = dataImpConf.getTargetTable();
                DataImpConfColmapExample dataImpConfColmapExample = new DataImpConfColmapExample();
                DataImpConfColmapExample.Criteria criteria = dataImpConfColmapExample.createCriteria();
                criteria.andCfgIdEqualTo(cfgId);
                List<Map<String,Object>> list = importConfigMapper.colImpData(paramMap);
                List<String> colList = new ArrayList<>();
                List<String> pkStr = new ArrayList<>();
                List<Map<String,Object>> nullList = new ArrayList<>();
                StringBuffer name = new StringBuffer();
                List<Map<String,Object>> pkData = new ArrayList<>();
                for (int j = 0; j < list.size(); j++) {
                    Map<String,Object> objectMap = list.get(j);
                    colList.add(objectMap.get("colName").toString());
                    if ("1".equals(objectMap.get("pkFlag").toString())) {
                        pkStr.add(objectMap.get("sourceCol").toString());
                        if ("".equals(name.toString())) {
                            name.append(objectMap.get("colName").toString());
                        } else {
                            name.append("," + objectMap.get("colName").toString());
                        }
                        Map<String,Object> objectMap1 = new HashMap<>();
                        objectMap1.put("colName", objectMap.get("colName").toString());
                        pkData.add(objectMap1);
                    }
                    if ("0".equals(objectMap.get("nullFlag").toString())) {
                        Map<String,Object> objectMap2 = new HashMap<>();
                        objectMap2.put("colName", objectMap.get("colName").toString());
                        objectMap2.put("sourceCol", objectMap.get("sourceCol").toString());
                        nullList.add(objectMap2);
                    }
                }
                if ("BATXLSX".equals(dataImpConf.getCfgType())) {
                    String path = dataImpConf.getDataSource();
                    File dir = new File(path);
                    if (dir.isDirectory()) {
                        List<String> allFile = new ArrayList<>();
                        allFile = getAllFiles(dir, allFile);
                        StringBuilder importInfo = new StringBuilder();
                        if ("OVERWRITE".equals(mode)) {
                            paramMap.put("targetTab", targetTab);
                            importConfigMapper.delAllData(paramMap);
                        }
                        int total = 0;
                        for (int l = 0; l < allFile.size(); l++) {
                            String path0 = allFile.get(l);
                            File file = new File(path0);
                            input = new FileInputStream(file);
                            String fileName = file.getName();
                            Workbook workbook = null;
                            if (fileName.endsWith("xls")) {
                                workbook = new HSSFWorkbook(input);
                            } else if (fileName.endsWith("xlsx")) {
                                workbook = new XSSFWorkbook(input);
                            }
                            boolean hasErr = false;                                                      //存在错误标志位
                            StringBuilder errInfo = new StringBuilder();
                            Integer seq = importConfigMapper.selMaxSeq(paramMap);
                            Sheet sheet = workbook.getSheetAt(0);
                            int rows = sheet.getPhysicalNumberOfRows();
                            List<String> key=new ArrayList<>();
                            List<Map<String, Object>> arrList = new ArrayList<>();
                            errInfo.append("<br/><br/><br/>*** 开始检查"+ workbook.getSheetName(0) +" ***");
                            for (int i = beginRow - 1; i < rows; i++) {
                                Row row = sheet.getRow(i);
                                if (row != null) {
                                    // 校验主键重复
                                    boolean rowFlag=true;
                                    StringBuffer rowBuffer = new StringBuffer();
                                    StringBuffer buffer = new StringBuffer();
                                    if (rowBuffer.toString().length()==0){
                                        rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;第<b>"+(i+1)+"</b>行数据:");
                                    }
                                    for (int j = 0; j < pkStr.size(); j++) {
                                        int colSeq = beginCol + Integer.parseInt(pkStr.get(j)) - 2;
                                        String cellValue = ExcelUtil.getStringCellValue(row.getCell(colSeq));
                                        buffer.append(cellValue);
                                        pkData.get(j).put("value", cellValue);
                                    }
                                    if (key.contains(buffer.toString())){
                                        rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + name.toString() + "字段数据重复");
                                        rowFlag=false;
                                    }else {
                                        if ("ADDEND".equals(mode) && pkData.size() > 0) {
                                            String sql = "select * from " + targetTab + "where";
                                            String str = "";
                                            for (int t = 0; t < pkData.size(); t++) {
                                                if ("".equals(str)) {
                                                    str = str + pkData.get(t).get("colName").toString() + "=" + pkData.get(t).get("value").toString();
                                                } else {
                                                    str = str + "and" + pkData.get(t).get("colName").toString() + "=" + pkData.get(t).get("value").toString();
                                                }
                                            }
                                            sql = sql + str;
                                            Map<String,Object> map2 = new HashMap<>();
                                            map2.put("sql", sql);
                                            List<Map<String,Object>> list1 = importConfigMapper.selPkData(map2);
                                            if (list1 != null && list1.size() > 0) {
                                                rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" + name.toString() + "数据库字段数据重复");
                                                rowFlag=false;
                                            } else {
                                                key.add(buffer.toString());
                                            }
                                        } else {
                                            key.add(buffer.toString());
                                        }
                                    }
                                    Map<String, Object> mapData = new HashMap<>();
                                    for (int j = beginCol - 1; j < seq + (beginCol - 1); j++) {
                                        Cell cell = row.getCell(j);
                                        String val = ExcelUtil.getStringCellValue(cell);
                                        for (int t = 0; t < nullList.size(); t++) {
                                            int m = beginCol + Integer.parseInt(nullList.get(t).get("sourceCol").toString()) - 2;
                                            if (j == m && StringUtil.isEmpty(val)) {
                                                rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + nullList.get(t).get("colName").toString() + ">不能为空");
                                                rowFlag=false;
                                            }
                                        }
                                        if (!StringUtil.isEmpty(val)) {
                                            for (int t = 0; t < list.size(); t++) {
                                                if ("NOSTR".equals(list.get(t).get("sourceColType").toString())) {
                                                    if (!StringUtil.isEmpty(list.get(t).get("transFunc"))) {
                                                        val = list.get(t).get("transFunc").toString();
                                                    } else {
                                                        val = "";
                                                    }
                                                }
                                                int colSeq = Integer.parseInt(list.get(t).get("sourceCol").toString());
                                                if (colSeq + beginCol - 2 == j) {
                                                    if (!StringUtil.isEmpty(list.get(t).get("transFunc")) && !"NOSTR".equals(list.get(t).get("sourceColType").toString())) {
                                                        mapData.put(list.get(t).get("colName").toString(), val + "@@_&&" + list.get(t).get("transFunc").toString());
                                                    } else {
                                                        mapData.put(list.get(t).get("colName").toString(), val + "@@_&&nofunction");
                                                    }
                                                    if (!StringUtil.isEmpty(list.get(t).get("checkType"))) {
                                                        String checkType = list.get(t).get("checkType").toString();
                                                        String formula = list.get(t).get("formula").toString();
                                                        if ("VALIN".equals(checkType)) {
                                                            //列表
                                                            String[] strings = formula.split(",");
                                                            boolean flag = false;
                                                            for (int s = 0; s < strings.length; s++) {
                                                                if (strings[s].equals(val)) {
                                                                    flag = true;
                                                                    break;
                                                                }
                                                            }
                                                            if (!flag) {
                                                                rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值不在[" + formula + "]列表范围内");
                                                                rowFlag=false;
                                                            }
                                                        } else if ("VALGT".equals(checkType)) {
                                                            //大于
                                                            if (Integer.parseInt(val) > Integer.parseInt(formula)) {
                                                            } else {
                                                                rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值不大于" + formula);
                                                                rowFlag=false;
                                                            }
                                                        } else if ("VALLT".equals(checkType)) {
                                                            //小于
                                                            if (Integer.parseInt(val) < Integer.parseInt(formula)) {
                                                            } else {
                                                                rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值不小于" + formula);
                                                                rowFlag=false;
                                                            }
                                                        } else if ("VALGTE".equals(checkType)) {
                                                            //大于等于
                                                            if (Integer.parseInt(val) > Integer.parseInt(formula) || Integer.parseInt(val) == Integer.parseInt(formula)) {
                                                            } else {
                                                                rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值小于" + formula);
                                                                rowFlag=false;
                                                            }
                                                        } else if ("VALLTE".equals(checkType)) {
                                                            //小于等于
                                                            if (Integer.parseInt(val) < Integer.parseInt(formula) || Integer.parseInt(val) == Integer.parseInt(formula)) {
                                                            } else {
                                                                rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值大于" + formula);
                                                                rowFlag=false;
                                                            }
                                                        } else if ("VALREG".equals(checkType)) {
                                                            //正则
                                                            if (!val.matches(formula)) {
                                                                rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">正则公式不匹配");
                                                                rowFlag=false;
                                                            }

                                                        } else if ("FORMULA".equals(checkType)) {
                                                            //公式

                                                        } else if ("VALEQUAL".equals(checkType)) {
                                                            //等于
                                                            if (val != formula) {
                                                                rowBuffer.append("<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<" + list.get(t).get("colName").toString() + ">值不等于" + formula);
                                                                rowFlag=false;
                                                            }
                                                        }
                                                    }


                                                }

                                            }
                                        }
                                    }
                                    if (rowFlag) {
                                        //将行错误信息记录到全局错误信息中
                                        arrList.add(mapData);
                                    }else {
                                        errInfo.append(rowBuffer.toString());

                                    }
                                }
                            }
                            if (!hasErr) {
                                String colStr = "";
                                for (int j = 0; j < colList.size(); j++) {
                                    if ("".equals(colStr)) {
                                        colStr = colList.get(j);
                                    } else {
                                        colStr = colStr + "," + colList.get(j);
                                    }
                                }
                                //String sql0 = "insert into " + targetTab + " (" + colStr + ")" + " values";
                                String sql0 = "insert into " + targetTab + " (" + colStr + ")";
                                String valStr = "";
                                Map<String, Object> map3 = new HashMap<>();
                                if (arrList.size() > 1000) {
                                    for (int j = 0; j < arrList.size(); j++) {
                                        Map<String, Object> map0 = arrList.get(j);
                                        //String a = "(";
                                        String a = "select ";
                                        for (int k = 0; k < colList.size(); k++) {
                                            String ssm = map0.get(colList.get(k)).toString();
                                            String[] b = ssm.split("@@_&&");
                                            String insertVal = "";
                                            if ("nofunction".equals(b[1])) {
                                                insertVal = b[0];
                                            } else {
                                                insertVal = b[0];
                                                if (b[1].indexOf("to_date") > -1) {
                                                    String[] strings = b[1].split("\\(");
                                                    if (strings.length > 1) {
                                                        String[] strings1 = b[1].split("\\(")[1].split("\\)");
                                                        if (strings1.length > 0) {
                                                            String reg = strings1[0];
                                                            insertVal = DateTimeUtil.matchDate(b[0], reg);
                                                        }
                                                    }
                                                } else {
                                                }
                                            }
                                    /*if ("(".equals(a)) {
                                        a = a + "'" + insertVal + "'";
                                    } else {
                                        a = a + ",'" + insertVal + "'";
                                    }*/
                                            if ("select ".equals(a)) {
                                                a = a + "'" + insertVal + "'";
                                            } else {
                                                a = a + ",'" + insertVal + "'";
                                            }
                                        }
                                        //a = a + ")";
                                        a = a + " from dual";
                                        if ("".equals(valStr)) {
                                            valStr = valStr + a;
                                        } else {
                                            //valStr = valStr + "," + a;
                                            valStr = valStr + " union all " + a;
                                        }
                                        if ((j != 0 && j % 1000 == 0) || j == arrList.size() - 1) {
                                            String sql = sql0 + valStr;
                                            map3.put("sql", sql);
                                            importConfigMapper.insertData(map3);
                                            valStr = "";
                                        }
                                    }
                                } else {
                                    for (int j = 0; j < arrList.size(); j++) {
                                        Map<String, Object> map0 = arrList.get(j);
                                        //String a = "(";
                                        String a = "select ";
                                        for (int k = 0; k < colList.size(); k++) {
                                            String ssm = map0.get(colList.get(k)).toString();
                                            String[] b = ssm.split("@@_&&");
                                            String insertVal = "";
                                            if ("nofunction".equals(b[1])) {
                                                insertVal = b[0];
                                            } else {
                                                insertVal = b[0];
                                                if (b[1].indexOf("to_date") > -1) {
                                                    String[] strings = b[1].split("\\(");
                                                    if (strings.length > 1) {
                                                        String[] strings1 = b[1].split("\\(")[1].split("\\)");
                                                        if (strings1.length > 0) {
                                                            String reg = strings1[0];
                                                            insertVal = DateTimeUtil.matchDate(b[0], reg);
                                                        }
                                                    }
                                                } else {
                                                }
                                            }
                                    /*if ("(".equals(a)) {
                                        a = a + "'" + insertVal + "'";
                                    } else {
                                        a = a + ",'" + insertVal + "'";
                                    }*/
                                            if ("select ".equals(a)) {
                                                a = a + "'" + insertVal + "'";
                                            } else {
                                                a = a + ",'" + insertVal + "'";
                                            }
                                        }
                                        //a = a + ")";
                                        a = a + " from dual";
                                        if ("".equals(valStr)) {
                                            valStr = valStr + a;
                                        } else {
                                            //valStr = valStr + "," + a;
                                            valStr = valStr + " union all " + a;
                                        }
                                    }
                                    //valStr = valStr + ";";
                                    String sql = sql0 + valStr;
                                    map3.put("sql", sql);
                                    importConfigMapper.insertData(map3);
                                }
                            }
                            if (hasErr) {
                                importInfo.append(errInfo.toString());
                            /*mapList.put("code", -1);
                            mapList.put("msg", errInfo.toString());*/
                            } else {
                                total = total + arrList.size();
                                StringBuilder rightInfo = new StringBuilder();
                                rightInfo.append("<br/>");
                                rightInfo.append("**********   <span style='color:green;font-weight:bold;'>文档"+ fileName +"导入结果：成功</span>   **********");
                                rightInfo.append("<br/><br/> 共计导入 <span style='color:blue;font-weight:bold;'>" + arrList.size() + "</span> 条" + workbook.getSheetAt(0).getSheetName() + "数据");
                            /*mapList.put("code", 0);
                            mapList.put("msg", rightInfo.toString());*/
                                importInfo.append(rightInfo.toString());
                            }
                        }
                        importInfo.append("<br/><br/> 总计导入 <span style='color:blue;font-weight:bold;'>" + total + "</span> 条数据");
                        mapList.put("code", 0);
                        mapList.put("msg", importInfo.toString());
                    } else {
                        mapList.put("code", 0);
                        mapList.put("msg", "源文件路径请填写正确目录路径");
                    }
                } else if ("BATDBTABLE".equals(dataImpConf.getCfgType())) {
                    String sourceTab = dataImpConf.getDataSource();
                    String searchCol = "";
                    String insertCol = "";
                    Map<String, Object> map = new HashMap<>();
                    for (int k = 0; k < list.size(); k++) {
                        if ("".equals(insertCol)) {
                            insertCol = insertCol + list.get(k).get("colName").toString();
                        } else {
                            insertCol = insertCol + "," + list.get(k).get("colName").toString();
                        }
                        if ("NOSTR".equals(list.get(k).get("sourceColType").toString())) {
                            if (!StringUtil.isEmpty(list.get(k).get("transFunc"))) {
                                if ("".equals(searchCol)) {
                                    searchCol = searchCol + "'" + list.get(k).get("transFunc").toString() + "'";
                                } else {
                                    searchCol = searchCol + ", '" + list.get(k).get("transFunc").toString() + "'";
                                }
                            } else {
                                if ("".equals(searchCol)) {
                                    searchCol = searchCol + "''";
                                } else {
                                    searchCol = searchCol + ", ''";
                                }
                            }
                        } else {
                            if ("".equals(searchCol)) {
                                searchCol = searchCol + list.get(k).get("sourceCol").toString();
                            } else {
                                searchCol = searchCol + "," + list.get(k).get("sourceCol").toString();
                            }
                        }
                        if (!"NOSTR".equals(list.get(k).get("sourceColType").toString()) && !StringUtil.isEmpty(list.get(k).get("transFunc"))) {
                            map.put(list.get(k).get("sourceCol").toString(), list.get(k).get("transFunc").toString());
                        }
                    }
                    String sql1 = "select " + searchCol + " from " + sourceTab;
                    paramMap.put("sql", sql1);
                    List<Map<String,Object>> list1 = importConfigMapper.selPkData(paramMap);
                    for (int j = 0; j < list1.size(); j++) {
                        for (int k = 0; k < list.size(); k++) {
                            if (!StringUtil.isEmpty(map.get(list.get(k).get("sourceCol").toString())) && !StringUtil.isEmpty(list1.get(j).get(list.get(k).get("sourceCol").toString()))) {
                                String val = list1.get(j).get(list.get(k).get("sourceCol").toString()).toString();
                                String method = map.get(list.get(k).get("sourceCol").toString()).toString();
                                if (method.indexOf("to_date") > -1) {
                                    String[] strings = method.split("\\(");
                                    if (strings.length > 1) {
                                        String[] strings1 = method.split("\\(")[1].split("\\)");
                                        if (strings1.length > 0) {
                                            String reg = strings1[0];
                                            val = DateTimeUtil.matchDate(val, reg);
                                            list1.get(j).put(list.get(k).get("sourceCol").toString(), val);
                                        }
                                    }
                                } /*else if () {

                                }*/
                            }
                        }
                    }
                    if ("OVERWRITE".equals(mode)) {
                        paramMap.put("targetTab", targetTab);
                        importConfigMapper.delAllData(paramMap);
                    }
                    String sql2 = "insert into " + targetTab + " (" + insertCol + ") ";
                    String insertStr = "";
                    for (int j = 0; j < list1.size(); j++) {
                        String str = "select ";
                        for (int k = 0; k < list.size(); k++) {
                            if ("NOSTR".equals(list.get(k).get("sourceColType").toString())) {
                                if (!StringUtil.isEmpty(list.get(k).get("transFunc"))) {
                                    if ("select ".equals(str)) {
                                        str = str + "'" + list.get(k).get("transFunc").toString() + "'";
                                    } else {
                                        str = str + ", '" + list.get(k).get("transFunc").toString() + "'";
                                    }
                                } else {
                                    if ("select ".equals(str)) {
                                        str = str + "''";
                                    } else {
                                        str = str + ", ''";
                                    }
                                }
                            } else {
                                if ("select ".equals(str)) {
                                    str = str + "'" + list1.get(j).get(list.get(k).get("sourceCol").toString()).toString() + "'";
                                } else {
                                    str = str + ", '" + list1.get(j).get(list.get(k).get("sourceCol").toString()).toString() + "'";
                                }
                            }
                        }
                        str = str + " from dual";
                        if ("".equals(insertStr)) {
                            insertStr = insertStr + str;
                        } else {
                            insertStr = insertStr + " union all " + str;
                        }
                        if (list1.size() > 1000 && j != 0) {
                            if (j % 1000 == 0 || j == list1.size() - 1) {
                                String sql3 = sql2 + insertStr;
                                paramMap.put("sql", sql3);
                                importConfigMapper.insertData(paramMap);
                                insertStr = "";
                            }
                        } else if (j == list1.size() - 1) {
                            String sql3 = sql2 + insertStr;
                            paramMap.put("sql", sql3);
                            importConfigMapper.insertData(paramMap);
                            insertStr = "";
                        }
                    }
                    mapList.put("code", 0);
                    mapList.put("msg", "导入成功，共计导入" + list1.size() + "条数据");
                } else if ("BATFILEGBK".equals(dataImpConf.getCfgType())) {

                } else if ("BATFILEU8".equals(dataImpConf.getCfgType())) {

                } else if ("BATDIR".equals(dataImpConf.getCfgType())) {

                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            mapList.put("code", -1);
            mapList.put("msg", e.getMessage());
        } finally {
            if (null != input) {
                try {
                    input.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return Result.ok(mapList);
    }

    /**
     * 获取导入配置下拉菜单
     * @return
     */
    public Result getImportConfigList(){
        return Result.ok(importConfigMapper.getImportConfigList());
    }
}
